/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nsc.utilities;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletContext;
import javax.sql.DataSource;

/**
 *
 * @author Hue
 */
public class DBUtilities {
    
    public static Connection getConnection(final ServletContext context, final String sqlLogfile) throws Exception {
        String url = (String) context.getAttribute(Constants.MYSQL_URL);
        if(url != null && !url.isEmpty()) {
            String user = (String) context.getAttribute(Constants.MYSQL_USER);
            String password = (String) context.getAttribute(Constants.MYSQL_PASSWORD);
            Connection con = DriverManager.getConnection(url, user, password);
            con.setAutoCommit(false);
            return new DBConnection(con, sqlLogfile);
        }
        return getConnection(context, Constants.JNDI_MYSQLDB, sqlLogfile);
    }
    
    private static Connection getConnection(final ServletContext context, final String strDsName, final String sqlLogfile) throws Exception {
        Connection con = null;
        boolean bValid = false;
        while(!bValid) {
            final DataSource dataSource = getDataSource(context, strDsName);
            con = dataSource.getConnection();
            // check the connection before using
            bValid = true;//isValid(con);
            if(!bValid) {
                con.close();
            }
        }
        con.setAutoCommit(false);
        
        return new DBConnection(con, sqlLogfile);
    }
    
    private static DataSource getDataSource(final ServletContext context, final String strDsName) throws Exception {
        if(context == null) {
            return (DataSource) new InitialContext().lookup(strDsName);
        }
        final String key = "DBUtilities.getDataSource";
        Context ctx = (Context) context.getAttribute(key);
        if(ctx == null) {
            final Context initCtx = new InitialContext();
            ctx = (Context) initCtx.lookup("java:comp/env");
            context.setAttribute(key, ctx);
        }
        final DataSource ds = (DataSource) ctx.lookup(strDsName);
        
        return ds;
    }
    
    public static String findDefaultValue(final Connection con, final String modPK, final String vparContext, final String vparKey) throws java.sql.SQLException {
        final String strQuery = "Select vpar_default_value From view_parameter Where vpar_mod_fk = ? And vpar_context = ? And vpar_key = ?";
        final PreparedStatement pStmt = con.prepareStatement(strQuery);
        pStmt.setString(1,modPK);
        pStmt.setString(2,vparContext);
        pStmt.setString(3,vparKey);
        final ResultSet rs = pStmt.executeQuery();
        String value = null;
        if(rs.next()) {
            value = rs.getString(1);
        }
        pStmt.close();

        return value;
    }
    
    public static void saveDefaultValue(final Connection con, final String modPK, final String vparContext, final String vparKey, final String vparTopContext, final String value) throws java.sql.SQLException {
        //find the vpar_pk
        String strQuery = "Select VPAR_PK From view_parameter Where vpar_mod_fk = ? And vpar_context = ? And vpar_key = ?";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        pStmt.setString(1,modPK);
        pStmt.setString(2,vparContext);
        pStmt.setString(3,vparKey);
        final ResultSet rs = pStmt.executeQuery();
        BigDecimal vparPK = null;
        if(rs.next()) {
            vparPK = rs.getBigDecimal(1);
        }
        pStmt.close();
        //insert new record into the table view_parameter if it doesn't exist.
        if(vparPK == null) {
            strQuery = "insert into view_parameter(VPAR_PK, VPAR_MOD_FK, VPAR_CONTEXT, VPAR_KEY, VPAR_TOPCONTEXT, VPAR_DEFAULT_VALUE) " + 
                       "values(SQ_VPAR_PK.nextval, ?, ?, ?, ?, ?)";
            int iIndex = 1;
            pStmt = con.prepareStatement(strQuery); 
            pStmt.setString(iIndex++, modPK);
            pStmt.setString(iIndex++, vparContext);
            pStmt.setString(iIndex++, vparKey);
            pStmt.setString(iIndex++, vparTopContext);
            pStmt.setString(iIndex++, value);
            pStmt.executeUpdate();            
            pStmt.close();
        }
        else {
            strQuery = "update view_parameter set vpar_default_value = ? where vpar_pk = ?";
            pStmt = con.prepareStatement(strQuery); 
            pStmt.setString(1, value);
            pStmt.setBigDecimal(2,vparPK);
            pStmt.executeUpdate();            
            pStmt.close();
        }                
    }

    public static void enableDbmsOutput(final Connection conn, int bufferSize) {
        try {
            final CallableStatement stmt = conn.prepareCall("{ call sys.dbms_output.enable(?) }");
            stmt.setInt(1, bufferSize);
            stmt.execute();
            stmt.close();
        }
        catch(Exception e) {
            System.out.println("Problem occurred while trying to enable dbms_output! " + e.toString());
        }
    }
    
    public static String printDbmsOutput(final Connection conn) {
        final StringBuilder sb = new StringBuilder();
        try {
            final CallableStatement stmt = conn.prepareCall("{ call sys.dbms_output.get_line(?,?) }");
            stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
            stmt.registerOutParameter(2, java.sql.Types.NUMERIC);
            int status = 0;
            do {
                stmt.execute();
                String s = stmt.getString(1);
                if(s != null) {
                    sb.append(s).append("\r\n");
                }
                status = stmt.getInt(2);
            }
            while (status == 0);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Problem occurred during dump of dbms_output! " + e.toString());
        }
        return sb.toString();
    }
    
    public static void closeConnection(final Connection con) {
        if(con == null) {
            return;
        }
        try {
            if(con.isClosed()) {
                return;
            }
            //rollback the transaction
            con.rollback();
        }
        catch(SQLException sqle) {
            //do nothing
        }
        try {
            con.close();
        }
        catch(SQLException sqle) {
            //do nothing
        }
    }
}
